﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.BasicList
{
    public partial class panel_Part : UserControl
    {
        private INISettings mySetting;
        private SqlConnection myConnection;
        private SqlConnection masterConnection;
        
        public panel_Part()
        {
            InitializeComponent();

            this.mySetting = new INISettings();
            this.myConnection = new SqlConnection(mySetting.SQLConnectionString);
            this.masterConnection = new SqlConnection(mySetting.DB2ConnectionString);

            LoadData();

            this.Part.DoubleClick += Part_DoubleClick;
            this.Part.PopupMenuShowing += Part_PopupMenuShowing;
            this.partContext.ItemClicked += partContext_ItemClicked;
        }

        private void callEditForm()
        {
            DataRow row = Part.GetFocusedDataRow();
            frm_Edit_Part editForm = new frm_Edit_Part();
            editForm.part_no = row[0].ToString();
            if (editForm.LoadData())
            {
                //editForm.Show();
                editForm.ShowDialog();
                LoadData();
            }
            else
            {
                MessageBox.Show("Cannot load data for selected record");
            }
        }

        void Part_DoubleClick(object sender, EventArgs e)
        {
            callEditForm(); 
        }

        void partContext_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
        {
            this.partContext.Hide();            
            switch (e.ClickedItem.Name)
            {
                case "importSelection":
                    importDatabase();
                    break;
                case "addSelection":
                    frm_Add_Part add_form = new frm_Add_Part();
                    add_form.ShowDialog();
                    LoadData();
                    break;
                case "editSelection":
                    callEditForm();                    
                    break;
                case "deleteSelection":
                    deleteRecord();
                    break;
                case "refreshSelection":
                    LoadData();
                    break;
            }
        }

        void Part_PopupMenuShowing(object sender, DevExpress.XtraGrid.Views.Grid.PopupMenuShowingEventArgs e)
        {
            this.partContext.Show(MousePosition);
        }

        ///////////////////////////////////
        /// Load Data From Local Database
        ///////////////////////////////////
        private void LoadData()
        {
            DataSet sourceDataSet = new DataSet();
            INISettings mySettings = new INISettings();
            SQLManagement mySQLManagement = new SQLManagement();
            mySQLManagement.SQLConnectionString = mySettings.SQLConnectionString;
            string sql = "SELECT [222_Part].[PartNo], [222_Part].[PartNoPurchase], [222_Part].[PartEnglishName],"
                + " [222_Part].[PartVietNamName], [222_Customer].FullName, [222_Maker].EnglishName,"
                + " FirstSupplier.SupplierName, SecondSupplier.SupplierName, [222_Part].[IQID], [222_Part].[UnitID],"
                + " [222_Part].[IQUnit], [222_Part].[UnitExchange], [222_Part].[LeadTime], [222_Part].[MinQuantity],"
                + " [222_Part].[ROHS], [222_Part].[Material], [222_Part].[Weight], [222_Part].[PartSize],"
                + " [222_Part].[QuantityBasePrice], [222_Part].[ExportOrImport], [222_Part].[SafeQuantity],"
                + " [222_Part].[PType], [222_Part].[Class], [222_Part].[PackingNote], [222_Part].[Remark],"
                + " [222_Part].[Marume], [222_Country].[CountryName], [222_Part].Department"
                + " FROM [222_Part] LEFT OUTER JOIN"
                + " [222_Customer] ON [222_Part].CustomerID=[222_Customer].CustomerID LEFT OUTER JOIN"
                + " [222_Maker] ON [222_Part].MakerID=[222_Maker].MakerID LEFT OUTER JOIN"
                + " [222_Supplier] AS FirstSupplier ON [222_Part].SupplierID=FirstSupplier.SupplierID LEFT OUTER JOIN"
                + " [222_Supplier] AS SecondSupplier ON [222_Part].SecondSupplierID=SecondSupplier.SupplierID LEFT OUTER JOIN"
                + " [222_Country] ON [222_Part].[OCountry]=[222_Country].CountryID"
                + " ORDER BY [222_Part].[PartNo]";
            sourceDataSet = mySQLManagement.SQL_Query(sql);
            if (sourceDataSet != null)
            {
                gridPart.DataSource = sourceDataSet.Tables[0];                
                getUpdateInfo();
            }
            else
            {
                MessageBox.Show("There is no data for Part");
                DataTable newTable = new DataTable();
                newTable.Columns.Add("PartNo", typeof(int));
                newTable.Columns.Add("PartNoPurchase", typeof(string));
                newTable.Columns.Add("PartEnglishName", typeof(string));
                newTable.Columns.Add("PartVietNamName", typeof(string));
                newTable.Columns.Add("Customer", typeof(string));
                newTable.Columns.Add("Maker", typeof(string));
                newTable.Columns.Add("Supplier", typeof(string));
                newTable.Columns.Add("SecondSupplier", typeof(string));
                newTable.Columns.Add("IQID", typeof(string));
                newTable.Columns.Add("UnitID", typeof(string));
                newTable.Columns.Add("IQUnit", typeof(string));
                newTable.Columns.Add("UnitExchange", typeof(string));
                newTable.Columns.Add("LeadTime", typeof(string));
                newTable.Columns.Add("MinQuantity", typeof(string));
                newTable.Columns.Add("ROHS", typeof(string));
                newTable.Columns.Add("Material", typeof(string));
                newTable.Columns.Add("Weight", typeof(string));
                newTable.Columns.Add("PartSize", typeof(string));
                newTable.Columns.Add("QuantityBasePrice", typeof(string));
                newTable.Columns.Add("ExportOrImport", typeof(string));
                newTable.Columns.Add("SafeQuantity", typeof(string));
                newTable.Columns.Add("PType", typeof(string));
                newTable.Columns.Add("Class", typeof(string));
                newTable.Columns.Add("PackingNote", typeof(string));
                newTable.Columns.Add("Remark", typeof(string));
                newTable.Columns.Add("Marume", typeof(string));
                newTable.Columns.Add("OCountry", typeof(string));
                newTable.Columns.Add("Department", typeof(string));
                gridPart.DataSource = newTable;
            }
            Part.Columns[0].Caption = "Part No";
            Part.Columns[1].Caption = "Part No Purchase";
            Part.Columns[2].Caption = "Part English Name";
            Part.Columns[3].Caption = "Part VietNam Name";
            Part.Columns[4].Caption = "Customer";
            Part.Columns[5].Caption = "Maker";
            Part.Columns[6].Caption = "Supplier";
            Part.Columns[7].Caption = "Second Supplier";
            Part.Columns[8].Caption = "IQID";
            Part.Columns[9].Caption = "Unit ID";
            Part.Columns[10].Caption = "IQ Unit";
            Part.Columns[11].Caption = "Unit Exchange";
            Part.Columns[12].Caption = "Lead Time";
            Part.Columns[13].Caption = "Min Quantity";
            Part.Columns[14].Caption = "ROHS";
            Part.Columns[15].Caption = "Material";
            Part.Columns[16].Caption = "Weight";
            Part.Columns[17].Caption = "Part Size";
            Part.Columns[18].Caption = "Quantity BasePrice";
            Part.Columns[19].Caption = "ExportOrImport";
            Part.Columns[20].Caption = "Safe Quantity";
            Part.Columns[21].Caption = "PType";
            Part.Columns[22].Caption = "Class";
            Part.Columns[23].Caption = "Packing Note";
            Part.Columns[24].Caption = "Remark";
            Part.Columns[25].Caption = "Marume";
            Part.Columns[26].Caption = "OCountry";
            Part.Columns[27].Caption = "Department";
            Part.BestFitColumns();
        }

        /////////////////////////////////////
        /// Get Update Information
        /////////////////////////////////////
        private void getUpdateInfo()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT LastUpdate FROM [222_UpdateHistory] WHERE ID=2";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                if (dataReader.Read())
                {
                    this.updateDate.Text = dataReader["LastUpdate"].ToString();
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get update information");
            }
        }

        private void buttonRefresh_Click(object sender, EventArgs e)
        {
            LoadData();
        }


        private void buttonEdit_Click(object sender, EventArgs e)
        {
            callEditForm(); 
        }

        private void buttonImport_Click(object sender, EventArgs e)
        {
            importDatabase();
        }
        
        ////////////////////////////////////
        /// Update database From AS400
        ////////////////////////////////////

        private void importDatabase()
        {
            DataSet as400 = new DataSet();
            DataSet part = new DataSet();
            string sql = null;
            SqlCommand cmd;
            SqlDataAdapter adapter;
            DataTable part_db2;
            DataTable part_local;                
            try
            {
                //Get Data From AS400                
                masterConnection.Open();
                sql = "SELECT TOP 10 * FROM OPENQUERY(AS400_MPFLIB, 'SELECT MPIT00.ITHNBN, CONCAT(MPIT00.ITSHBN, MPIT00.ITNAM2) AS ITSHBN,"
                    + " MPIT00.ITNAM1, MPIT00.ITMCOD, MPIT00.ITSICD, SUBSTRING(MPIT00.ITFILL,20,6) AS ITFILL, MPIT30.I3IQNO,"
                    + " MPIT00.ITHTAN, MPIT00.ITTNI1, MPIT00.ITLDTM, SUBSTRING(MPIT30.I3AFIL,40,8) AS I3AFIL, MPIT00.ITHNTP,"
                    + " MPIT00.ITMRYO"
                    + " FROM MPIT00 JOIN MPIT30 ON MPIT30.I3HNBN=MPIT00.ITHNBN')";
                cmd = new SqlCommand(sql, masterConnection);
                adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(as400);
                part_db2 = as400.Tables[0];
                adapter.Dispose();
                cmd.Dispose();
                masterConnection.Close(); 
                
                //Get Data From Local Database
                myConnection.Open();
                sql = "SELECT * FROM [222_Part]";
                cmd = new SqlCommand(sql, myConnection);
                adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(part, "222_Part");
                part_local = part.Tables["222_Part"];
                                
                foreach (DataRow as400_row in part_db2.Rows)
                {
                    DataRow[] current_row = part_local.Select("PartNo='" + as400_row["ITHNBN"].ToString() + "'");
                    if (current_row.Length > 0)
                    {
                        foreach (DataRow local_row in current_row)
                        {
                            local_row.BeginEdit();
                            local_row["PartNoPurchase"] = as400_row["ITSHBN"].ToString();
                            local_row["PartEnglishName"] = as400_row["ITNAM1"].ToString();
                            local_row["MakerID"] = as400_row["ITMCOD"].ToString();
                            local_row["SupplierID"] = as400_row["ITSICD"].ToString();
                            local_row["SecondSupplierID"] = as400_row["ITFILL"].ToString();
                            local_row["IQID"] = as400_row["I3IQNO"].ToString();                             
                            local_row["UnitID"] = as400_row["ITHTAN"].ToString();
                            local_row["IQUnit"] = as400_row["ITTNI1"].ToString();
                            int itldtm = 0;
                            if (Int32.TryParse(as400_row["ITLDTM"].ToString(), out itldtm))
                            {
                                local_row["LeadTime"] = itldtm;
                            }                            
                            int i3afil = 0;
                            if (Int32.TryParse(as400_row["I3AFIL"].ToString(), out i3afil))
                            {
                                local_row["MinQuantity"] = i3afil;
                            }                            
                            local_row["PType"] = as400_row["ITHNTP"].ToString();
                            int itmryo = 0;
                            if (Int32.TryParse(as400_row["ITMRYO"].ToString(), out itmryo))
                            {
                                local_row["Marume"] = itmryo;
                            }
                            local_row.EndEdit();
                        }
                    }
                    else
                    {
                        DataRow new_row = part_local.NewRow();
                        new_row["PartNo"] = as400_row["ITHNBN"].ToString();
                        new_row["PartNoPurchase"] = as400_row["ITSHBN"].ToString();
                        new_row["PartEnglishName"] = as400_row["ITNAM1"].ToString();
                        new_row["MakerID"] = as400_row["ITMCOD"].ToString();
                        new_row["SupplierID"] = as400_row["ITSICD"].ToString();
                        new_row["SecondSupplierID"] = as400_row["ITFILL"].ToString();
                        new_row["IQID"] = as400_row["I3IQNO"].ToString();
                        new_row["UnitID"] = as400_row["ITHTAN"].ToString();
                        new_row["IQUnit"] = as400_row["ITTNI1"].ToString();
                        int itldtm = 0;
                        if (Int32.TryParse(as400_row["ITLDTM"].ToString(), out itldtm))
                        {
                            new_row["LeadTime"] = itldtm;
                        }
                        int i3afil = 0;
                        if (Int32.TryParse(as400_row["I3AFIL"].ToString(), out i3afil))
                        {
                            new_row["MinQuantity"] = i3afil;
                        }
                        new_row["PType"] = as400_row["ITHNTP"].ToString();
                        int itmryo = 0;
                        if (Int32.TryParse(as400_row["ITMRYO"].ToString(), out itmryo))
                        {
                            new_row["Marume"] = itmryo;
                        }
                        part_local.Rows.Add(new_row);
                    }
                }  
                               

                SqlCommandBuilder objCommandBuilder = new SqlCommandBuilder(adapter);
                //Update to Local Database
                adapter.Update(part, "222_Part");
                
                adapter.Dispose();
                cmd.Dispose();
                myConnection.Close();
                
                //Update History
                myConnection.Open();
                sql = "UPDATE [222_UpdateHistory] SET LastUpdate=GETDATE() WHERE ID=1";
                SqlCommand cmd_sql = new SqlCommand(sql, myConnection);                
                cmd_sql.ExecuteNonQuery();
                cmd_sql.Dispose();
                myConnection.Close();

                LoadData();

                MessageBox.Show("Database has been updated successfull.");
                                
            }
            catch (Exception ex)
            {
                if (masterConnection.State == ConnectionState.Open)
                {
                    masterConnection.Close();
                }

                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show(ex.Message);
                //MessageBox.Show("Cannot connect to AS400");
            }
        }

        private void deleteRecord()
        {
            if (MessageBox.Show("Are you really to delete selected record now?", "Confirm delete", MessageBoxButtons.YesNo) == DialogResult.Yes)
            {
                DataRow row = Part.GetFocusedDataRow();
                try
                {
                    myConnection.Open();
                    string sql = "DELETE [222_Part] WHERE PartNo='" + row[0].ToString() + "'";
                    SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                    cmd_sql.ExecuteNonQuery();
                    cmd_sql.Dispose();
                    myConnection.Close();
                    MessageBox.Show("Part deleted success.");
                    LoadData();
                }
                catch //(Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    //MessageBox.Show(ex.Message);
                    MessageBox.Show("Cannot delete Part. Please try again.");
                }
            }
        }

        private void buttonDelete_Click(object sender, EventArgs e)
        {
            deleteRecord();
        }

        private void buttonAdd_Click(object sender, EventArgs e)
        {
            frm_Add_Part add_form = new frm_Add_Part();
            add_form.ShowDialog();
            LoadData();
        }
    }
}
